using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VMLKOPF
    {
        public DataRow Convert2MLKOPF(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryMLKOPF(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["MLMANDANT"] = " ".PadRight(2 ); 
         dwReturn["MLNR"] = " ".PadRight(8 ); 
         dwReturn["MLPOS"] = " ".PadRight(3 ); 
         dwReturn["MLINHABERK"] = " ".PadRight(10 ); 
         dwReturn["MLKNTRDAT"] = " ".PadRight(0 ); 
         dwReturn["MLKNTRZEIT"] = " ".PadRight(0 ); 
         dwReturn["MLINHABER"] = " ".PadRight(10 ); 
         dwReturn["MLARCH"] = " ".PadRight(8 ); 
         dwReturn["MLORDNER"] = " ".PadRight(8 ); 
         dwReturn["MLRUEDAT"] = " ".PadRight(0 ); 
         dwReturn["MLRUEZEIT"] = " ".PadRight(0 ); 
         dwReturn["MLWVDAT"] = " ".PadRight(0 ); 
         dwReturn["MLWVZEIT"] = " ".PadRight(0 ); 
         dwReturn["MLEMPF"] = " ".PadRight(10 ); 
         dwReturn["MLABS"] = " ".PadRight(8 ); 
         dwReturn["MLABSDAT"] = " ".PadRight(0 ); 
         dwReturn["MLABSZEIT"] = " ".PadRight(0 ); 
         dwReturn["MLBETREFF"] = " ".PadRight(50 ); 
         dwReturn["MLMANDANTA"] = " ".PadRight(2 ); 
         dwReturn["MLMANDANTE"] = " ".PadRight(2 ); 
         dwReturn["MLEMPFART"] = " ".PadRight(1 ); 
         dwReturn["MLANF"] = " ".PadRight(1 ); 
         dwReturn["MLSPSTA"] = " ".PadRight(1 ); 
         dwReturn["MLDRINGL"] = " ".PadRight(1 ); 
         dwReturn["MLPERS"] = " ".PadRight(1 ); 
         dwReturn["MLWEIDURCH"] = " ".PadRight(8 ); 
         dwReturn["MLPAPDAT"] = " ".PadRight(0 ); 
         dwReturn["MLLOEDAT"] = " ".PadRight(0 ); 
         dwReturn["MLARCHKZ"] = " ".PadRight(1 ); 
         dwReturn["MLRUEKZ"] = " ".PadRight(1 ); 
         dwReturn["MLUKGRD"] = " ".PadRight(4 ); 
         dwReturn["MLLEITWEG"] = " ".PadRight(10 ); 
         dwReturn["MLUKDATEI"] = " ".PadRight(8 ); 
         dwReturn["MLUKPRIMI"] = " ".PadRight(150 ); 
         dwReturn["MLRUEPROG"] = " ".PadRight(8 ); 
         dwReturn["MLDRBENID"] = " ".PadRight(8 ); 
         dwReturn["MLDRAUFTNR"] = " ".PadRight(4 ); 
         dwReturn["MLSENDPROG"] = " ".PadRight(8 ); 
         dwReturn["MLFILLER"] = " ".PadRight(206 ); 
         dwReturn["MLAENDZL"] = " ".PadRight(0 ); 
         dwReturn["MLAENDBEN"] = " ".PadRight(8 ); 
         dwReturn["MLAENDDAT"] = " ".PadRight(0 ); 
         dwReturn["MLNEUBEN"] = " ".PadRight(8 ); 
         dwReturn["MLNEUDAT"] = " ".PadRight(0 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapMLKOPF(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveMLKOPF(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryMLKOPF(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapMLKOPF(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertMLKOPF(dwLocal, ITXDBVERSION);
            }
            UpdateMLKOPF(dwLocal);
        }
        public void InsertMLKOPF(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO MLKOPF(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryMLKOPF(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM MLKOPF WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM MLKOPF  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "MLKOPF";
            Return = Result;
            return Return;
        }
        public void DeleteMLKOPF(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM MLKOPF   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateMLKOPF(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE MLKOPF SET "
                    +"MLMANDANT= :MLMANDANT,"
                    +"MLNR= :MLNR,"
                    +"MLPOS= :MLPOS,"
                    +"MLINHABERK= :MLINHABERK,"
                    +"MLKNTRDAT= :MLKNTRDAT,"
                    +"MLKNTRZEIT= :MLKNTRZEIT,"
                    +"MLINHABER= :MLINHABER,"
                    +"MLARCH= :MLARCH,"
                    +"MLORDNER= :MLORDNER,"
                    +"MLRUEDAT= :MLRUEDAT,"
                    +"MLRUEZEIT= :MLRUEZEIT,"
                    +"MLWVDAT= :MLWVDAT,"
                    +"MLWVZEIT= :MLWVZEIT,"
                    +"MLEMPF= :MLEMPF,"
                    +"MLABS= :MLABS,"
                    +"MLABSDAT= :MLABSDAT,"
                    +"MLABSZEIT= :MLABSZEIT,"
                    +"MLBETREFF= :MLBETREFF,"
                    +"MLMANDANTA= :MLMANDANTA,"
                    +"MLMANDANTE= :MLMANDANTE,"
                    +"MLEMPFART= :MLEMPFART,"
                    +"MLANF= :MLANF,"
                    +"MLSPSTA= :MLSPSTA,"
                    +"MLDRINGL= :MLDRINGL,"
                    +"MLPERS= :MLPERS,"
                    +"MLWEIDURCH= :MLWEIDURCH,"
                    +"MLPAPDAT= :MLPAPDAT,"
                    +"MLLOEDAT= :MLLOEDAT,"
                    +"MLARCHKZ= :MLARCHKZ,"
                    +"MLRUEKZ= :MLRUEKZ,"
                    +"MLUKGRD= :MLUKGRD,"
                    +"MLLEITWEG= :MLLEITWEG,"
                    +"MLUKDATEI= :MLUKDATEI,"
                    +"MLUKPRIMI= :MLUKPRIMI,"
                    +"MLRUEPROG= :MLRUEPROG,"
                    +"MLDRBENID= :MLDRBENID,"
                    +"MLDRAUFTNR= :MLDRAUFTNR,"
                    +"MLSENDPROG= :MLSENDPROG,"
                    +"MLFILLER= :MLFILLER,"
                    +"MLAENDZL= :MLAENDZL,"
                    +"MLAENDBEN= :MLAENDBEN,"
                    +"MLAENDDAT= :MLAENDDAT,"
                    +"MLNEUBEN= :MLNEUBEN,"
                    +"MLNEUDAT= :MLNEUDAT,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("MLMANDANT", OracleType.NVarChar).Value = dwData["MLMANDANT"].ToString(); 
          or1.Parameters.Add("MLNR", OracleType.NVarChar).Value = dwData["MLNR"].ToString(); 
          or1.Parameters.Add("MLPOS", OracleType.NVarChar).Value = dwData["MLPOS"].ToString(); 
          or1.Parameters.Add("MLINHABERK", OracleType.NVarChar).Value = dwData["MLINHABERK"].ToString(); 
          or1.Parameters.Add("MLKNTRDAT", OracleType.NVarChar).Value = dwData["MLKNTRDAT"].ToString(); 
          or1.Parameters.Add("MLKNTRZEIT", OracleType.NVarChar).Value = dwData["MLKNTRZEIT"].ToString(); 
          or1.Parameters.Add("MLINHABER", OracleType.NVarChar).Value = dwData["MLINHABER"].ToString(); 
          or1.Parameters.Add("MLARCH", OracleType.NVarChar).Value = dwData["MLARCH"].ToString(); 
          or1.Parameters.Add("MLORDNER", OracleType.NVarChar).Value = dwData["MLORDNER"].ToString(); 
          or1.Parameters.Add("MLRUEDAT", OracleType.NVarChar).Value = dwData["MLRUEDAT"].ToString(); 
          or1.Parameters.Add("MLRUEZEIT", OracleType.NVarChar).Value = dwData["MLRUEZEIT"].ToString(); 
          or1.Parameters.Add("MLWVDAT", OracleType.NVarChar).Value = dwData["MLWVDAT"].ToString(); 
          or1.Parameters.Add("MLWVZEIT", OracleType.NVarChar).Value = dwData["MLWVZEIT"].ToString(); 
          or1.Parameters.Add("MLEMPF", OracleType.NVarChar).Value = dwData["MLEMPF"].ToString(); 
          or1.Parameters.Add("MLABS", OracleType.NVarChar).Value = dwData["MLABS"].ToString(); 
          or1.Parameters.Add("MLABSDAT", OracleType.NVarChar).Value = dwData["MLABSDAT"].ToString(); 
          or1.Parameters.Add("MLABSZEIT", OracleType.NVarChar).Value = dwData["MLABSZEIT"].ToString(); 
          or1.Parameters.Add("MLBETREFF", OracleType.NVarChar).Value = dwData["MLBETREFF"].ToString(); 
          or1.Parameters.Add("MLMANDANTA", OracleType.NVarChar).Value = dwData["MLMANDANTA"].ToString(); 
          or1.Parameters.Add("MLMANDANTE", OracleType.NVarChar).Value = dwData["MLMANDANTE"].ToString(); 
          or1.Parameters.Add("MLEMPFART", OracleType.NVarChar).Value = dwData["MLEMPFART"].ToString(); 
          or1.Parameters.Add("MLANF", OracleType.NVarChar).Value = dwData["MLANF"].ToString(); 
          or1.Parameters.Add("MLSPSTA", OracleType.NVarChar).Value = dwData["MLSPSTA"].ToString(); 
          or1.Parameters.Add("MLDRINGL", OracleType.NVarChar).Value = dwData["MLDRINGL"].ToString(); 
          or1.Parameters.Add("MLPERS", OracleType.NVarChar).Value = dwData["MLPERS"].ToString(); 
          or1.Parameters.Add("MLWEIDURCH", OracleType.NVarChar).Value = dwData["MLWEIDURCH"].ToString(); 
          or1.Parameters.Add("MLPAPDAT", OracleType.NVarChar).Value = dwData["MLPAPDAT"].ToString(); 
          or1.Parameters.Add("MLLOEDAT", OracleType.NVarChar).Value = dwData["MLLOEDAT"].ToString(); 
          or1.Parameters.Add("MLARCHKZ", OracleType.NVarChar).Value = dwData["MLARCHKZ"].ToString(); 
          or1.Parameters.Add("MLRUEKZ", OracleType.NVarChar).Value = dwData["MLRUEKZ"].ToString(); 
          or1.Parameters.Add("MLUKGRD", OracleType.NVarChar).Value = dwData["MLUKGRD"].ToString(); 
          or1.Parameters.Add("MLLEITWEG", OracleType.NVarChar).Value = dwData["MLLEITWEG"].ToString(); 
          or1.Parameters.Add("MLUKDATEI", OracleType.NVarChar).Value = dwData["MLUKDATEI"].ToString(); 
          or1.Parameters.Add("MLUKPRIMI", OracleType.NVarChar).Value = dwData["MLUKPRIMI"].ToString(); 
          or1.Parameters.Add("MLRUEPROG", OracleType.NVarChar).Value = dwData["MLRUEPROG"].ToString(); 
          or1.Parameters.Add("MLDRBENID", OracleType.NVarChar).Value = dwData["MLDRBENID"].ToString(); 
          or1.Parameters.Add("MLDRAUFTNR", OracleType.NVarChar).Value = dwData["MLDRAUFTNR"].ToString(); 
          or1.Parameters.Add("MLSENDPROG", OracleType.NVarChar).Value = dwData["MLSENDPROG"].ToString(); 
          or1.Parameters.Add("MLFILLER", OracleType.NVarChar).Value = dwData["MLFILLER"].ToString(); 
          or1.Parameters.Add("MLAENDZL", OracleType.NVarChar).Value = dwData["MLAENDZL"].ToString(); 
          or1.Parameters.Add("MLAENDBEN", OracleType.NVarChar).Value = dwData["MLAENDBEN"].ToString(); 
          or1.Parameters.Add("MLAENDDAT", OracleType.NVarChar).Value = dwData["MLAENDDAT"].ToString(); 
          or1.Parameters.Add("MLNEUBEN", OracleType.NVarChar).Value = dwData["MLNEUBEN"].ToString(); 
          or1.Parameters.Add("MLNEUDAT", OracleType.NVarChar).Value = dwData["MLNEUDAT"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
